﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using PPAP.BO;
using PPAP.Model;
using System.Text.RegularExpressions;

namespace PPAP.Web.Sampling
{
    
    public partial class ImportPart : PageBase
    {
        SupplierBO bo = new SupplierBO();
        DataTable data = new DataTable();
        PARTBO pbo = new PARTBO();
        PPARBO ppbo = new PPARBO();
        LogBO lbo = new LogBO();
        UsersBO ubo = new UsersBO();
        MailTemplateBO mbo = new MailTemplateBO();
        SupplierAdminBO sabo = new SupplierAdminBO();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            try
            {
                filePath = UploadFile.FileName;
                string name = filePath.Substring(filePath.LastIndexOf("\\") + 1).ToString();
                string exp = name.Substring(name.LastIndexOf(".") + 1);

                if (exp.ToLower() != "xls" && exp.ToLower() != "xlsx" && exp.ToLower() != "csv")
                {
                    ClientScript.RegisterStartupScript(this.GetType(), "key1", "<script>alert('File format not(.xls|.xlsx|.csv)！');</script>");
                }
                else
                {
                    string FileNam_Time = "";
                    string FName = DateTime.Now.ToString("yyyyMMddHHmmss")+name;
                    FileNam_Time = FName;//日期和名称组合
                    string path = HttpContext.Current.Request.MapPath("../FileUpload/ImportExcel/" + base.CurrentUser.Name + "/");
                    string url = "../FileUpload/QYRD/" + CurrentUser.Name + "/new" + FileNam_Time;
                    DirectoryInfo upDir = new DirectoryInfo(path);
                    if (!upDir.Exists)
                    {
                        upDir.Create();
                    }
                    filePath = path + FileNam_Time;
                    UploadFile.SaveAs(filePath);
                    ImportExcel(filePath);

                }
            }
            catch (Exception ex)
            {
                AlertMessage("Err："+ex.Message.ToString());
                return;
            }
            finally
            {
                
            }

        }

        private void ImportExcel(string excelFile)
        {
            DataSet ds = new DataSet();
            OleDbConnection myConn = new OleDbConnection();
            try
            {
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
                if(rbt2007.Checked)
                strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+excelFile+@";Extended Properties=""Excel 12.0;HDR=YES;""";
                myConn = new OleDbConnection(strCon);
                myConn.Open();
                System.Data.DataTable schemaTable = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });     //获取表名

                if (schemaTable.Rows.Count > 0)
                {
                    string tablename = "Sheet1" + "$";//获到表名 
                    string sql = "select * from [" + tablename + "]";  //从工作表查询。
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, myConn);
                    da.Fill(ds); 

                    DataTable dt = new DataTable();
                    dt.Columns.Add("rownum");  //rownum
                    dt.Columns.Add("name1");  //BDNT Part No.
                    dt.Columns.Add("name2");   //BDNT Part Name
                    dt.Columns.Add("name3");   //Supplier Name
                    dt.Columns.Add("name4");  //BDNT Revision No.
                    dt.Columns.Add("name5");  //BDNT Revision Status
                    dt.Columns.Add("name6");  //Color
                    dt.Columns.Add("name7");  //Planed PPAR date
                    dt.Columns.Add("name8");  //QE
                    dt.Columns.Add("name9"); //Supplier Admin
                    dt.Columns.Add("name10");  //结果
                    ds.Tables[0].Columns.Add("supplierid");
                    int num = 0;
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        num++;
                        DataRow r = dt.NewRow();
                        r[0] = num;
                        r[1] = dr[0]; //rownum
                        r[2] = dr[1];//part no
                        r[3] = dr[2];//part name
                        r[4] = dr[3];//supplier name
                        r[5] = dr[4];//status
                        r[6] = dr[5]; //color
                        r[7] = dr[6]; //date
                        r[8] = dr[7]; //qe
                        r[9] = dr[8];//Supplier Admin
                        string supplierid = Check(r[3].ToString());
                        string  bopp = Checkpp(r[1].ToString(),supplierid,dr[5].ToString(),dr[7].ToString(),r[9].ToString());
                        r[10] = bopp; //JIEGUO
                        dr["supplierid"] = bopp == "<font color='blue'>success</font>" ? supplierid : "0";
                        dt.Rows.Add(r);
                    }
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                    if (ds.Tables[0].Rows.Count > 0)
                        btnSave.Visible = true;
                    else
                        btnSave.Visible = false;
                }

                Session["dsexcel"] = ds;
                btnSave.Visible = true;
                //var q=from p in ds.Tables[0] group p by p  ds.Tables[0].g
                List<PartImport> list = new List<PartImport>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    PartImport im = new PartImport
                    {
                        Color = dr[5].ToString(),
                        Partname = dr[2].ToString(),
                        Partno = dr[1].ToString(),
                        Qe = dr[7].ToString(),
                        Revisionstatus = dr[4].ToString(),
                        Supplieradmin = dr[8].ToString(),
                        Suppliercode = dr[3].ToString()
                    };
                    list.Add(im);
                }
                var q = from p in list
                        group p by p.Qe into g
                        select new
                        {
                            g.Key,
                            NumProducts = g.Count()
                        };
                //List<PartImport> list2 = q.ToList().ConvertAll<PartImport>(Items=>new PartImport({});
               
            }
            catch (Exception ex)
            {
                AlertMessage("Err:"+ex.Message.ToString());
                GC.Collect();
            }
            finally
            {
                myConn.Close();
                myConn.Dispose();
            }
        }

        public bool CheckEmail(string Email)
        {
            if (!getRegexInput(Email, @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"))
            {
                return false;
            }
            else
            {
                return true;
            }

        }

        public bool getRegexInput(string str, string regexStr)//str是要被验证的字符串，regexStr为正则表达式
        {
            Regex rStr = new Regex(regexStr);
            bool re = false;
            if (rStr.IsMatch(str))
            {
                re = true;
            }
            return re;
        }

        private string Check(string SupplierCode)
        {
            data = bo.GetList(" and SUPPLIERCODE='" + SupplierCode + "'").Tables[0];
            if (data.Rows.Count > 0)
            {
                return data.Rows[0]["id"].ToString();
                //return "<font color='blue'>success</font>";
            }
            else
                return "0";
                //return "<font color='red'>Supplier Name Does not exist</font>";
        }

        private string Checkpp(string PartNo,string supplierid,string color,string qe,string supplieradmin)
        {
            if (ubo.GetList("  position='Q-engineer' and USERNAME='"+qe+"' ").Tables[0].Rows.Count == 0)
                return "<font color='red'>Q-engineer Does not exist</font>";
            else if (pbo.GetList(" BDNTPARTNO='" + PartNo + "' and COLOR='"+color+"' ").Tables[0].Rows.Count > 0)
                return "<font color='red'>(BDNT Part No.&color) already exists</font>";
            else if(supplierid=="0")
                return "<font color='red'>Supplier Code Does not exist</font>";
            else if(!CheckEmail(supplieradmin))
                return "<font color='red'>Supplier Admin format wrong</font>";
            return CheckPartNo(PartNo) ? "<font color='blue'>success</font>" : "<font color='red'>Format is not standard（BDNT Part No.）</font>";

        }

        /// <summary>
        /// 验证PartNo合法性
        /// </summary>
        /// <param name="PartNo"></param>
        /// <returns></returns>
        private bool CheckPartNo(string PartNo)
        {
            //if (PartNo.Length < 11)
            //    return false;
            //if (PartNo.Substring(0, 4).ToUpper() != "TIG-")
            //    return false;
            //else return true;
            return true;
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSave_Click(object sender, EventArgs e)
        {
            string message = "";
            try
            {
                if (Session["dsexcel"] != null)
                {
                    PART p = new PART();
                    PPAR pp = new PPAR();
                    
                    int num = 0;
                    DataSet ds = Session["dsexcel"] as DataSet;
                    MailTemplate mail15 = mbo.GetModel(15);  //给供应商管理员发送用户名和密码
                    MailTemplate mail16 = mbo.GetModel(16); //给QE发送任务提醒
                    string sendQE = "";  //已发送的QE人员
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        if (dr["supplierid"].ToString() != "0")
                        {
                            if (ubo.CheckEmailNotAdmin(dr[8].ToString()))
                            {
                                message += dr[8].ToString() + " Already exists \n";
                            }
                            else
                            {
                                p.BDNTPARTNO = dr[0].ToString();
                                p.BDNTPARTNAME = dr[1].ToString();
                                p.BDNTREVISIONNO = dr[3].ToString();
                                p.BDNTREVISIONSTATUS = dr[4].ToString();
                                p.COLOR = dr[5].ToString();
                                p.Supplieradmin = dr[8].ToString();
                                pp.Planedppardate = DateTime.Parse(dr[6].ToString());
                                pp.Qeuser = dr[7].ToString();
                                p.CREATETIME = DateTime.Now;
                                p.CREATEUSER = CurrentUser.Id;
                                p.SUPPLIERID = int.Parse(dr["supplierid"].ToString());
                                int partid = pbo.AddExcel(p);
                                if (partid > 0)
                                {
                                    num++;
                                    pp.PARTID = partid;
                                    pp.STATUS = 0;
                                    pp.PPARDATE = DateTime.Now;
                                    ppbo.AddPP(pp);
                                    if (mail16 != null)
                                    {
                                        try
                                        {
                                            if (!sendQE.Contains(pp.Qeuser))
                                            {
                                                base.SendEmailToUser(pp.Qeuser, mail16.TITLE, mail16.CONTENT, null);
                                                sendQE += pp.Qeuser + ";";
                                                lbo.WriteLog(CurrentUser.Id, "Send Email:" + pp.Qeuser);
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            lbo.WriteLog(CurrentUser.Id, "Send Email:" + ex.Message.ToString());
                                        }
                                    }
                                }
                                if (sabo.GetList(" USERNAME='" + dr[8].ToString() + "' ").Tables[0].Rows.Count == 0)
                                {
                                    SupplierAdmin sa = new SupplierAdmin();
                                    sa.PASSWORD = "123456";
                                    sa.SUPPLIERID = int.Parse(dr["supplierid"].ToString());
                                    sa.USERNAME = dr[8].ToString();
                                    int said = sabo.Add(sa);
                                    if (mail15 != null && said > 0)
                                    {
                                        try
                                        {
                                            base.SendEmailToUser(sa.USERNAME, mail15.TITLE, mail15.CONTENT.Replace("{Username}", sa.USERNAME).Replace("{Password}", sa.PASSWORD), null);
                                            lbo.WriteLog(CurrentUser.Id, "Send Email:" + sa.USERNAME);
                                        }
                                        catch (Exception ex)
                                        {
                                            lbo.WriteLog(CurrentUser.Id, "Send Email:" + ex.Message.ToString());
                                        }
                                    }
                                }

                            }
                        }
                    }
                    if (num > 0)
                    {
                        lbo.WriteLog(CurrentUser.Id, "ImportPart:"+num);
                        AlertMessage("" + num + " parts are imported to PPAP system successfully");
                    }
                    else
                        AlertMessage("Saved(" + 0 + ")");
                    btnSave.Visible = false;
                    lblMessage.Text = message;
                }
            }
            catch (Exception ex)
            {
                AlertMessage(ex.Message.ToString());
            }
            finally
            {
                Session["dsexcel"] = null;
            }
        }
        
    }
}